<?php
	function DbConnect()
	{
		$connection = oci_connect($username = 'yqiao',
							  $password = 'chang526',
							  $connection_string = '//oracle.cise.ufl.edu/orcl');
		if($connection)
			return $connection;
		else
			return null;
	}

	function DbConnectionClose($connection,  $statement)
	{
		oci_free_statement($statement);
		oci_close($connection);
	}
  
	function get_course_info($cid) {
		$con = DbConnect();
		$query = "select * from course where cid = " . $cid;
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		$course = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS);
		DbConnectionClose($con,  $stmt);
		return $course;
	}
	
	function get_course_info_with_con($con, $cid) {
		$query = "select * from course where cid = " . $cid;
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		$course = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS);
		oci_free_statement($stmt);
		return $course;
	}
	
	function get_group_info_with_con($con, $gid) {
		$query = "select * from groups where gid = " . $gid;
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		$group = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS);
		oci_free_statement($stmt);
		return $group;
	}
	
	function get_posts_with_con($con, $cid){
		$query = "select CPID, SUBJECT, CONTENT, POST_TIME, AUTHOR, FIRST_NAME from coursepost, student where courseid = " . $cid . " and coursepost.status!='deleted' and sid=author order by POST_TIME desc, CPID desc";
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		oci_fetch_all($stmt, $posts, null, null, OCI_FETCHSTATEMENT_BY_ROW);
		oci_free_statement($stmt);
		return $posts;
	}
	function get_post_replies_with_con($con, $cpid){
		$query = "select CRID, CONTENT, POST_TIME, AUTHOR, FIRST_NAME, LAST_NAME, PROFILE_PIC from coursereply, student where reply_to = " . $cpid . " and coursereply.status!='deleted' and sid=author order by POST_TIME, CRID asc";
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		oci_fetch_all($stmt, $replies, null, null, OCI_FETCHSTATEMENT_BY_ROW);
		oci_free_statement($stmt);
		return $replies;
	}
	
	function get_post_info_with_con($con, $cpid){
		$query = "select CPID, SUBJECT, CONTENT, POST_TIME, AUTHOR, COURSEID, FIRST_NAME, LAST_NAME, PROFILE_PIC from coursepost, student where author=sid and cpid = " . $cpid;
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		$cpost = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS);
		oci_free_statement($stmt);
		
		$query = "select SCORE from coursereview where cpid = " . $cpid;
		$stmt2 = oci_parse($con, $query);
		oci_execute($stmt2);
		$review = oci_fetch_array($stmt2, OCI_ASSOC+OCI_RETURN_NULLS);
		$cpost["SCORE"] = $review["SCORE"];
		oci_free_statement($stmt2);
		
		$query = "select PRICE, BOOK_CONDITION from bookshare where cpid = " . $cpid;
		$stmt3 = oci_parse($con, $query);
		oci_execute($stmt3);
		$share = oci_fetch_array($stmt3, OCI_ASSOC+OCI_RETURN_NULLS);
		$cpost["PRICE"] = $share["PRICE"];
		$cpost["BOOK_CONDITION"] = $share["BOOK_CONDITION"];
		oci_free_statement($stmt3);
		
		return $cpost;
	}
	
	function get_post_info($cpid){
		$con = DbConnect();
		$cpost = get_post_info_with_con($con, $cpid);
		oci_close($con);
		return $cpost;
	}
	
	function add_course($con, $sid, $cid) {
		$query = "insert into StuCourse(StudentID, CourseID) values(" . $sid . ", " . $cid . ")";
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		oci_free_statement($stmt);
	}
	
	function quit_course($con, $sid, $cid){
		$query = "delete from StuCourse where StudentID=" . $sid . " and CourseID=" . $cid;
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		oci_free_statement($stmt);
	}
	
	function is_selected_course($con, $sid, $cid){
		$query = "select * from StuCourse where StudentID = " . $sid . " and CourseID=" . $cid;
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		$answer = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS);
		oci_free_statement($stmt);
		return $answer["STUDENTID"]!="";
	}
	
	function is_selected_group($con, $sid, $gid){
		$query = "select * from StuGroup where StudentID = " . $sid . " and GroupID=" . $gid;
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		$answer = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS);
		oci_free_statement($stmt);
		return $answer["STUDENTID"]!="";
	}
	// check if course id already exists in DB
	function db_course_exists($con, $cid) {
		$query = "select cid from course where cid = " . $cid;
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		$row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS);
		$exist = $row["CID"];
		oci_free_statement($stmt);
		return $exist;
	}
	
	function db_group_exists($con, $gid) {
		$query = "select gid from groups where gid = " . $gid;
		$stmt = oci_parse($con, $query);
		oci_execute($stmt);
		$row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_NULLS);
		$exist = $row["GID"];
		oci_free_statement($stmt);
		return $exist;
	}
	
	function delete_course_with_con($con, $cid){
		
	}
?>
